package com.jy.api.service.impl;

import com.jy.api.service.TableService;
import com.jy.api.util.JodaUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.data.redis.connection.RedisConnection;
import org.springframework.data.redis.core.RedisCallback;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

@Slf4j
@Service
public class TableServiceImpl implements TableService {
    /**
     * 需要归集的表，有强依赖的表，要放在前面执行，否则删除数据不会成功
     */
    private static final String[] tables = new String[]{"order_item", "order", "payment", "stock_change_transaction"};
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private StringRedisTemplate stringRedisTemplate;

    @Override
    public List<String> backup() {
        List<String> sqls = new ArrayList<>();
        for (String table : tables) {
            final String tableKey = String.format("backup(%s)", table);
            final String value = stringRedisTemplate.opsForValue().get(tableKey);
            if (null != value) {
                log.info(String.format("表%s无需继续归集数据了...", value));
                continue;
            }

            String firstOrderSQL = String.format("SELECT `add_time` FROM `%s` ORDER BY `add_time` ASC LIMIT 1", table);
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(firstOrderSQL);
            if (!maps.isEmpty()) {
                Map<String, Object> map = maps.get(0);
                Timestamp addTime = (Timestamp) map.get("add_time");
                if (null != addTime) {
                    long time = addTime.getTime() + 8 * 60 * 60 * 1000;//服务器时区加8小时，搞不懂为啥不用中国时区

                    //大于100天的自动归集成老数据:8640000
                    if ((JodaUtils.getTimestamp() - (time / 1000)) > 8640000) {
                        String yyyyMM = JodaUtils.timeLongToString(time, "yyyyMM");
                        String yyyyMMdd = JodaUtils.timeLongToString(time, "yyyy-MM-dd");
                        log.info(addTime.toString());
                        log.info(yyyyMM);
                        log.info(yyyyMMdd);

                        final List<String> strings = tableMove(table, yyyyMM, yyyyMMdd);
                        sqls.addAll(strings);
                    } else {
                        stringRedisTemplate.opsForValue().set(tableKey, table);
                        stringRedisTemplate.expire(tableKey, 5, TimeUnit.HOURS);
                        //jdbcTemplate.execute(String.format("optimize TABLE `%s`", table));
                    }
                }
            }
        }
        return sqls;
    }

    private List<String> tableMove(String table, String month, String day) {
        jdbcTemplate.execute("SET FOREIGN_KEY_CHECKS = 0");
        List<String> sqls = new ArrayList<>();
        try {
            String createMonthTable = String.format("CREATE TABLE IF NOT EXISTS `%s_%s` like `%s`", table, month, table);
            log.info(createMonthTable);
            jdbcTemplate.execute(createMonthTable);
            sqls.add(createMonthTable);
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            String insertIntoMonthTable = String.format("INSERT INTO `%s_%s` SELECT * FROM `%s` WHERE `add_time` <= '%s 23:59:59'", table, month, table, day);
            log.info(insertIntoMonthTable);
            jdbcTemplate.execute(insertIntoMonthTable);
            sqls.add(insertIntoMonthTable);
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            String delete = String.format("DELETE FROM `%s` WHERE `add_time` <= '%s 23:59:59'", table, day);
            log.info(delete);
            jdbcTemplate.execute(delete);
            sqls.add(delete);
        } catch (Exception e) {
            e.printStackTrace();
        }
        jdbcTemplate.execute("SET FOREIGN_KEY_CHECKS = 1");
        return sqls;
    }
}
